package com.sxf.common.tools;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public class JDBCTools {

	public static List<Map<String, Object>> queryForList(String sql,
			Object[] args) throws Exception {
		Connection conn = getConnection();
		List<Map<String, Object>> list = queryForList(sql, args, conn);
		if (conn != null) {
			conn.close();
			conn = null;
		}
		return list;
	}

	public static List<Map<String, Object>> queryForList(String sql,
			Object[] args, Connection conn) throws Exception {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (conn != null && !conn.isClosed()) {
			PreparedStatement psmt = conn.prepareStatement(sql);
			if (args != null) {
				for (int i = 0; i < args.length; i++) {
					psmt.setObject(i + 1, args[i]);
				}
			}
			ResultSet rs = psmt.executeQuery();
			ResultSetMetaData rsd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= rsd.getColumnCount(); i++) {
					map.put(rsd.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
			rs.close();
			psmt.close();
		}
		return list;
	}

	public static Map<String, Object> execProc(String sql, Object[] inParam,
			Integer[] sqlTypes) throws Exception {
		Connection conn = getConnection();
		conn.setAutoCommit(false);
		Map<String, Object> map = execProc(sql, inParam, sqlTypes, conn);
		if (conn != null) {
			conn.commit();
			conn.close();
			conn = null;
		}
		return map;
	}

	public static Map<String, Object> execProc(String sql, Object[] inParam,
			Integer[] sqlTypes, Connection conn) throws Exception {
		Map<String, Object> result = new HashMap<String, Object>();
		if (conn != null && !conn.isClosed()) {
			CallableStatement call = conn.prepareCall(sql);
			int i = 0;
			if (inParam != null && inParam.length > 0) {
				for (i = 0; i < inParam.length; i++) {
					call.setObject(i + 1, inParam[i]);
				}
			}
			if (sqlTypes != null) {
				for (int j = 0; j < sqlTypes.length; j++) {
					call.registerOutParameter(i + j + 1, sqlTypes[j]);
				}
			}
			boolean flag = call.execute();
			int m = 0;
			if (sqlTypes != null) {
				for (int j = 0; j < sqlTypes.length; j++) {
					Object o = call.getObject(i + j + 1);
					if (o != null && o instanceof ResultSet) {
						List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
						ResultSet rs = (ResultSet) o;
						rs.setFetchSize(100);
						ResultSetMetaData rsd = rs.getMetaData();
						while (rs.next()) {
							Map<String, Object> map = new LinkedHashMap<String, Object>();
							for (int k = 1; k <= rsd.getColumnCount(); k++) {
								map.put(rsd.getColumnName(k), rs.getObject(k));
							}
							list.add(map);
						}
						rs.close();
						result.put("RC" + j, list);
					} else {
						result.put("RC" + j, o);
					}
				}
				m = sqlTypes.length;
			}

			while (flag) {
				ResultSet rs = call.getResultSet();
				rs.setFetchSize(100);
				ResultSetMetaData rsd = rs.getMetaData();
				List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
				while (rs != null && rs.next()) {
					Map<String, Object> map = new LinkedHashMap<String, Object>();
					for (int k = 1; k <= rsd.getColumnCount(); k++) {
						map.put(rsd.getColumnName(k), rs.getObject(k));
					}
					list.add(map);
				}
				result.put("RC" + (m++), list);
				flag = call.getMoreResults(); // 检查是否存在更多结果集
			}
			call.close();
		}
		return result;
	}

	public static Connection getConnection() throws Exception {
		DataSource ds = (DataSource) SpringTools.getWebApplicationContext()
				.getBean("dataSource");
		return ds.getConnection();
	}

}
